﻿


/*存储过程-合并excel表*/
if exists(select 1 from sysobjects where xtype='P' and name='InfluxExlData') drop procedure InfluxExlData
go
Create proc InfluxExlData(@fromMonth varchar(2) ,@toMonth varchar(2),@year varchar(4))
as 
begin 
	declare @sql varchar(max),@sqlwehre varchar(max)
	set @sql=''
	if @fromMonth>@toMonth return 
	while @fromMonth<@toMonth+1
	begin 
		set @sql='insert into 补贴发放临时数据 select [编号],[补贴建筑面积],[当月发放金额],[当月补发金额],'+@year+','+@fromMonth+',[编号]+'''+@year+'''+'''+@fromMonth+''' from ['''+@year+'#'+@fromMonth+'$'']    '
		exec(@sql)
		set @fromMonth=@fromMonth+1		
	end 	 
	
end 
GO 

/*存储过程-临时表去重复*/
if exists(select 1 from sysobjects where xtype='P' and name='GetTempDataDistinct') drop procedure GetTempDataDistinct
go
Create proc GetTempDataDistinct
as 
begin  
	select distinct *  into #temptable from 补贴发放临时数据 where 编号 is not null
	delete   from  补贴发放临时数据
	insert into  补贴发放临时数据 select * from #temptable
	drop table #temptable
end 
GO 


/*存储过程-导入到SubsidyRecord函数*/
if exists(select 1 from sysobjects where xtype='P' and name='ImportData') drop proc ImportData
go
Create proc ImportData(@year int,@month int)
as 
begin 

	insert into SubsidyRecord
	SELECT 
	  [编号] as [adj_longid]
	  ,1 as [adj_checked]
      ,[当月发放金额] as [adj_nowmoney]
      ,dbo.getMoneyChange([编号],[当月发放金额],[年],[月]) as adj_moneychange -------[adj_moneychange]
      ,[补贴建筑面积] as [adj_nowarea]
      ,0-------[adj_iscontinue] 
      ,dbo.fixYearMonth([年],[月]) as [adj_starttime]      
      ,dbo.getStopTime([编号]) as [adj_stoptime]
      ,'' as [adj_remark]
      ,dbo.fixYearMonth([年],[月]) as [adj_date]
      ,dbo.getNoID([编号]) as [adj_noid]
      ,'' as [adj_reason]
  FROM 补贴发放临时数据 where [年]=@year and  [月]=@month  and dbo.getMoneyChange([编号],[当月发放金额],[年],[月])<>0 and [编号] is not null
/*
---错误原因：Select是1-5月一齐insert表SubsidyRecord，getMoneyChange无法选出2月份与1月份的差，此时1月份数据还未插入到表SubsidyRecord
---改用游标形式 ，游标是一条实时插入，后面数据可以与前面数据比较
*/
		/*
		
		*/
End
GO
--/*存储过程-修正差距：getMoneyChange  */
--if exists(select 1 from sysobjects where xtype='P' and name='correctMoneyChange') drop proc correctMoneyChange
--go
--create proc correctMoneyChange 
--as 
--begin 
--	/*自己跟自己比较，创建#SubsidyRecord 表影像*/
--	IF OBJECT_ID('tempdb..#SubsidyRecord') IS NOT  NULL  DROP TABLE #SubsidyRecord
--	select * into #SubsidyRecord from SubsidyRecord
--	declare @adj_id int, @adj_longid varchar(30),@adj_moneychange float,@adj_nowmoney float,@year int,@month int,@adj_date int
--		if CURSOR_STATUS('global','TempdataCur') =-3 and CURSOR_STATUS('local','TempdataCur') =-3 
--		begin 
--			declare TempdataCur cursor for select adj_id ,adj_longid,adj_moneychange,adj_nowmoney, adj_date from SubsidyRecord
--		end 
--		open TempdataCur
--		Fetch next from TempdataCur into @adj_id ,@adj_longid,@adj_moneychange,@adj_nowmoney, @adj_date
--		while(@@FETCH_STATUS=0)
--		begin 
--			declare @tempAdj_nowmoney float
--			select top 1 @tempAdj_nowmoney=adj_nowmoney from [SubsidyRecord] where adj_longid=@adj_longid and adj_date<@adj_date order by adj_date desc
--			if(@tempAdj_nowmoney is not null)
--			begin 
--				update [SubsidyRecord] set adj_moneychange=@adj_nowmoney-@tempAdj_nowmoney where  adj_id= @adj_id
--			end 
--		end 
--		close TempdataCur
--		deallocate TempdataCur
--end 
--GO
/*函数-某个人的补发金额变化*/
if exists(select * from sysobjects where  name='getMoneyChange' and xtype='FN' ) drop function getMoneyChange
go
Create function getMoneyChange(@ID nvarchar(255),@nowMoney float,@year int,@month int)
returns float
as 
begin 
	declare @adj_moneychange float
	declare @adj_id int	
	declare @thisdate int
	set @thisdate=dbo.fixYearMonth(@year,@month)
	select top 1 @adj_id=adj_id, @adj_moneychange=(@nowMoney-adj_nowmoney) from SubsidyRecord 
	where [adj_longid]=@ID and  adj_date<@thisdate order by adj_date desc
	--如果前面的月份数据没有，上面@adj_moneychange为null 
	if(@adj_moneychange is null)
		begin 
			set @adj_moneychange=@nowMoney
		end 
	return @adj_moneychange
end
GO

/*函数-得到终止时间*/
if exists(select * from sysobjects where  name='getStopTime' and xtype='FN' ) drop function getStopTime
go
Create function getStopTime(@id varchar(150))
returns int
as 
begin 
	declare @stoptime varchar(100),@year varchar(4),@month varchar(2)
	select @stoptime=[享受终止时间] from [标准享受类别表] where [编号]=@id
	set @year='20'+right(@stoptime,2)
	set @month=dbo.EnMonthToNum(substring(@stoptime,4,3))
	return cast(@year+@month as int)
end
----SELECT dbo.getStopTime('A05000003')
GO
/*函数-合并时间*/
if exists(select * from sysobjects where  name='fixYearMonth' and xtype='FN' ) drop function fixYearMonth
go
Create function fixYearMonth(@year int,@month int)
returns int
as 
begin 
	declare @str varchar(6)
	declare @monthstr varchar(2),@yearstr varchar(4)
	set @monthstr=cast(@month as varchar(2))
	set @yearstr=cast(@year as varchar(4))
	if @month<10  
	begin 
		set @monthstr='0'+@monthstr
	end 
	set @str=@yearstr+@monthstr
	return cast(@str as int);
end
GO

/*函数-得到临时编号*/
if exists(select * from sysobjects where  name='getNoID' and xtype='FN' ) drop function getNoID
go
Create function getNoID(@id varchar(150))
returns int
as 
begin 
	declare @noid int;
	select @noid=apply_noid from apply where apply_longid=@id
	return @noid
end 
----SELECT dbo.getNoID(1)
GO
/*函数-月份转换*/
if exists(select * from sysobjects where  name='EnMonthToNum' and xtype='FN' ) drop function EnMonthToNum
go
Create function EnMonthToNum(@m varchar(10))
returns varchar(10)
as 
begin 
	declare @mstr varchar(10)
	select @mstr=Case LOWER(@m)
					when 'jan' then  '01'
					when 'feb' then  '02'
					when 'mar' then  '03'
					when 'apr' then  '04'
					when 'may' then  '05'
					when 'jan' then  '06'
					when 'jul' then  '07'
					when 'aug' then  '08'
					when 'sep' then  '09'
					when 'oct' then  '10'
					when 'nov' then  '11'
					when 'dec' then  '12'					
				 end 
	return @mstr
end 
--select dbo.EnMonthToNum('APR')